#!/usr/bin/env python3.8
# -*- coding: utf-8 -*-
# @Author     : zh
# @Description: 导入csv,且可配置
#from odps import ODPS
#from odps.df import DataFrame
import datetime
import sys
#import time
#import pandas as pd
#from pandas import DataFrame
import psycopg2
from Connection_all_def import *
#from difflib import Differ
import json
# import tkinter
# import tkinter.messagebox #弹窗库
import tkinter
from tkinter.messagebox import *
from log_ex2 import * #日志模块
from colorama import init
init(autoreset=True)

sys.stdout = Logger(sys.stdout)


""" 取当前日期、月份、天、小时 """
time_new = datetime.datetime.now().strftime("%Y%m%d")
month_new = datetime.datetime.now().month
day_new = datetime.datetime.now().day
hour_judge = datetime.datetime.now().hour
week_time = datetime.datetime.now().weekday() + 1

""" 取当前时间的前一天日期 """
before_time = datetime.datetime.now() + datetime.timedelta(days=-1)
before_time_format = before_time.strftime("%Y%m%d")

#时间
now = time.strftime("%Y-%m-%d-%H-%M-%S")
now2 = format(now)

""" 需要执行的SQL语句 """
linshi_day_sql_sequence = f'''SELECT * from ana_air_station_monitor'''
sql_sequence = f"""SELECT * from mpp"""
weekends_3315_sql_sequence = f''' SELECT * from mpp '''
sql_seq_zao1 = """ SELECT id, menu_name, menu_url, parent_id, menu_order, level, menu_flag, show_flag, platform, system_id, menu_type, permission
FROM public.t_system_menu; """
sql_seq_zao2 = """ SELECT * FROM etl_value; """
sql_seq_zao3 = """ SELECT "用户名" from mpp """
sql_seq_wan1 = """ SELECT * from mpp """
sql_seq_wan2 = """  SELECT * from mpp"""
sql_seq_wan3 = """SELECT * from mpp  """
#sql_seq_app = f'SELECT * FROM '+schemas+'.'+table+';'


"""定义schemas参数--共用目录 """
fix_data_frame = read_canshu_data(os.path.join(abs_path_canshu_file, "schem.txt"))
schemas=fix_data_frame.iloc[0, 0]
table=fix_data_frame.iloc[0, 1]
sql_seq_app = f'SELECT * FROM '+schemas+'.'+table+';'
#sql_seq_talbname = """SELECT A .attname AS NAME FROM  pg_class AS C,  pg_attribute AS A WHERE  C .relname = 'ana_air_station_monitor' AND A .attrelid = C .oid AND A .attnum > 0;"""
#数据库获取字段
#sql_seq_talbname = """SELECT attname  FROM  pg_class AS C,  pg_attribute AS A WHERE  C .relname = '(ana_air_station_monitor)' AND attrelid = C .oid AND attnum > 0;"""

""" 连接naviacat数据库 """
fix_data_frame = read_canshu_data(os.path.join(abs_path_canshu_file, "database.txt"))
link_to_navicat_db = psycopg2.connect(database=fix_data_frame.iloc[0, 0], user=fix_data_frame.iloc[0, 1],
                                      password=fix_data_frame.iloc[0, 2], host=fix_data_frame.iloc[0, 3],
                                      port=fix_data_frame.iloc[0, 4])
fix_data_frame = read_canshu_data(os.path.join(abs_path_canshu_file, "database.txt"))
schemas=fix_data_frame.iloc[0, 5]

#json 表和本地文件对应
with open(r'./config/data.json', 'r') as f:
    data = json.load(f)

""" 创建一个游标 """
print("1，当前时间为：", datetime.datetime.now())
#psycopg2函数拿字段,创建游标
cur = link_to_navicat_db.cursor()
for table in data:
    #num = table.strip('\n')
    sql = f'''SELECT attname  FROM  pg_class AS C,  pg_attribute AS A WHERE  C .relname = '{table}' AND attrelid = C .oid AND attnum > 0;'''
    cur.execute(sql)
    rows = cur.fetchall()
#把rows 写进list
    rows=[i[0] for i in rows]
    print("\033[0;33m 2，此处是库里获取的字段名{}{}\033[0m".format(table,rows))
    # 从本地文件获取字段名
    tablefile = data[table]
    dataframe2 = pd.read_csv(r"./import/{}".format(tablefile))  # index_col=0 注释 变量拼接例子
    # 获取列名tolist()输出不是list
    dfc = dataframe2.columns.tolist()
    print("\033[0;36m 3，此处是本地csv获取的字段名{}{}\033[0m".format(table,dfc))
    #print(dfc)
    #print("\033[0;35m对比表{}字段顺序和名是否一致{}\033[0m".format(table,(dataframe2.columns == rows))) #== 如果一样，输出True，否则False
    #=========================上面已经完成基本人工对比功能，下面这一段是为了做判断流程========================
    dblen = len(rows)
    csvlen = len(dfc)
    if dblen != csvlen:
        print("5.1,dblen为{},和csvlen为{}，字段数量不相等，不入库，请挑出此表{}，手动入库,程序退出".format(dblen, csvlen, table))
        window = tkinter.Tk()
        window.withdraw()  # 退出默认 tk 窗口
        result = showwarning('警告',"库里表”{}“的字段个数为{},待导入csv里的字段个数为{}，字段数量不相等，请从data.json挑出表{}，然后手动入库，程序退出".format(table,dblen, csvlen, table))
        sys.exit()
    else:
        print("5.2,表{}字段数量相等，开始匹配字段名是否完全一致".format(table))
    #========================================上面是字段数对比
    jieguo = dataframe2.columns == rows
    print("这是为了找是否有false{}".format(jieguo)) #打印对比结果
    s = [s for s in jieguo if False == s]
    print("\033[0;31m 6,表括号里如果有[False]字样，则表示有字段不一致{}{}\033[0m".format(table,s))
    print("\033[0;35m 8,对比表{}字段顺序和名是否一致{}\033[0m".format(table, (dataframe2.columns == rows)))  # == 如果一样，输出True，否则False
    #===================================================

#=====================================
    lst2 = [False]
    if set(s) & set(lst2):
        print("'7.1,表{}字段名称有不一致，修改data.json文件,挑出此表继续'".format(table))
        #tkinter.messagebox.showwarning('警告', '表{}字段有不一致，json文件挑出此表继续'.format(table))
        window = tkinter.Tk()
        window.withdraw()  # 退出默认 tk 窗口
        result = showwarning('警告', '表{}字段名称有不一致，修改data.json文件,挑出此表继续,程序退出'.format(table))
        sys.exit()
        #print(f'字段检查完，: {result}')
    else:
        print("7.2,表{}没有字段名称不一致的情况，可以用工具入库".format(table))


#=======================================
    # print(jieguo.index(s[0]))
    # print(jieguo[jieguo.index(s[0])])
    # if dataframe2.columns == rows:
    #     print("字段顺序完全一致，准备入库")
    # else:
    #     print("字段顺序不一致，跳过表入库，可以尝试手动入库")

link_to_navicat_db.close()


#下面是清空导入表

print('----------------------------------------------------------------------------------------------')
print('|                                                                                            |')
print('|                                        下面是清空导入表                                       |')
print('|                                                                                            |')
print('----------------------------------------------------------------------------------------------')
sys.stdout = Logger(sys.stdout)
"""定义schemas参数--共用config目录 """
fix_data_frame = read_canshu_data(os.path.join(abs_path_canshu_file, "database.txt"))
host2=fix_data_frame.iloc[0, 3]
database2=fix_data_frame.iloc[0, 0] #只是为了打印
schemas=fix_data_frame.iloc[0, 5]
code=fix_data_frame.iloc[0, 6]
print(code)
#table=fix_data_frame.iloc[0, 1]


""" 连接naviacat数据库 """
fix_data_frame = read_canshu_data(os.path.join(abs_path_canshu_file, "database.txt"))
link_to_navicat_db = psycopg2.connect(database=fix_data_frame.iloc[0, 0], user=fix_data_frame.iloc[0, 1],
                                      password=fix_data_frame.iloc[0, 2], host=fix_data_frame.iloc[0, 3],
                                      port=fix_data_frame.iloc[0, 4])

root = tkinter.Tk()
root.withdraw()
#print(tkinter.messagebox.askokcancel("标题","字段检测没问题，是否确认入库{}.{}.{}，".format(host2,database2,schemas)))
now_playing = True
if tkinter.messagebox.askokcancel("标题","字段检测没问题，是否确认入库{}.{}.{}，".format(host2,database2,schemas)):
    print("8,开始导入表")
else:
    root.destroy()
    print("9，选择的取消，结束程序")
    sys.exit()
""" 创建一个游标 """
cur = link_to_navicat_db.cursor()

#验证引入的模块可用
# var1 = "这是表名:"+table+""
# print(var1)
# #this_file = f"{table}.csv"
# this_file = f".\import\{tablefile}"
# var2 = "这是带入的文件名:"+tablefile+""
# print(var2)

# Python 字典类型转换为 JSON 对象
# with open(r'./config/data.json', 'r') as f:
#     data = json.load(f)

#需要执行的sql

# this_copy_01 = f'''copy {table} from STDOUT with (FORMAT csv,DELIMITER ',',header true,quote '"');'''
# this_copy_02 = f'''truncate table public.{table}'''
# this_copy_03 = f'''select count(1) from public.{table}'''

#统计每个表导入前的数据量
#for table in open(r".\config\mtable.txt"):
for table in data:
    try:
        #this_copy_03 = f'''select count(1) from public.{table}''' #简单统计
        this_copy_03 = f'''select '{table}'as tablename , count(8) from {table}'''
        count1 = pd.read_sql_query(this_copy_03, con=link_to_navicat_db)
        print(count1)
    except Exception as e:
        print(e)
print("========10，上面是原始表更新前的数据量======")

#清空数据
#for table in open(r".\config\mtable.txt"): #这是去文件遍历
for table in data:
    try:
        var2 = "11，开始清空表:"+table+""
        print(var2)
        this_copy_02 = f'''truncate table public.{table}'''
        cur.execute("TRUNCATE TABLE %s" % table)
        #pd.read_sql_query(this_copy_02, con=link_to_navicat_db)
    except Exception as e2:
        print(e2)

#插入数据
for table in data:
    tablefile = data[table]
    # var3 = "xxxxxxxxxxxxxxxxxxxx:" + table + ""+var4
    # print(var3)
    with open(rf'./import/{tablefile}',encoding="%s"%(code)) as f:
        try:
            print("&&&&&&&&&&&&&&&开始copy导入数据&&&&&&&&&&&&&&&&&&&&&")
            var3 = "开始更新表:" + table + ""
            print(var3)
            this_copy_01 = f'''copy {table} from STDOUT with (FORMAT csv,DELIMITER ',',header true,quote '"');'''
            cur.copy_expert(this_copy_01, f)
        except Exception as e:
            print(e)
    try:
        cur.copy_expert(this_copy_03, f)
    except Exception as e:
        print (e)
#统计导入后
print("========12，下是表更新后的数据量======")
#for table in open(r".\config\mtable.txt"):
for table in data:
    try:
        #this_copy_03 = f'''select count(1) from public.{table}'''
        this_copy_03 = f'''select '{table}'as tablename , count(8) from {table}'''
        count1 = pd.read_sql_query(this_copy_03, con=link_to_navicat_db)
        print(count1)
    except Exception as e:
        print(e)

link_to_navicat_db.commit()
link_to_navicat_db.close()
